#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2024/3/4 15:13
# @Author  : 王凯
# @File    : send_wechat_msg.py
# @Project : scrapy_spider
import datetime
import sys
from pathlib import Path

import pandas as pd
import requests
from apscheduler.schedulers.blocking import BlockingScheduler
from loguru import logger
sys.path.append(Path(__file__).parent.parent.as_posix())
from components.config import NET_ROBOT_MYSQL_CONFIG, WFQ_SOURCE_MYSQL_CONFIG
from utils.db.mysqldb import MysqlDB

WECHAT_MSG_URL = "https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=ac1046c1-5e3b-4468-a4f6-496488f11606"


class SendWechatMsg:
    to_db = None
    wfq_source = None

    def __init__(self):
        self.init()

    def init(self):
        self.to_db = MysqlDB(
            ip=NET_ROBOT_MYSQL_CONFIG["MYSQL_IP"],
            port=NET_ROBOT_MYSQL_CONFIG['MYSQL_PORT'],
            db=NET_ROBOT_MYSQL_CONFIG['MYSQL_DB'],
            user_name=NET_ROBOT_MYSQL_CONFIG['MYSQL_USER_NAME'],
            user_pass=NET_ROBOT_MYSQL_CONFIG['MYSQL_USER_PASS'],
        )
        self.wfq_source = MysqlDB(
            ip=WFQ_SOURCE_MYSQL_CONFIG["MYSQL_IP"],
            port=WFQ_SOURCE_MYSQL_CONFIG['MYSQL_PORT'],
            db=WFQ_SOURCE_MYSQL_CONFIG['MYSQL_DB'],
            user_name=WFQ_SOURCE_MYSQL_CONFIG['MYSQL_USER_NAME'],
            user_pass=WFQ_SOURCE_MYSQL_CONFIG['MYSQL_USER_PASS'],
        )

    @staticmethod
    def create_table_with_pandas(datas, mappings=None):
        df = pd.DataFrame(datas)
        if mappings is None:
            mappings = {}
        if mappings:
            df = df.rename(columns=mappings)
        headers = df.columns.tolist()
        table = "|"
        for header in headers:
            table += f" {header} |"
        table += "\n|"
        for _ in headers:
            table += " ------- |"
        for row_dict in df.to_dict('records'):
            table += "\n|"
            for header in headers:
                table += f" {row_dict.get(header)} |"
        return table

    @staticmethod
    def wechat_send(msg):
        """markdown内容，最长不超过4096个字节，必须是utf8编码"""
        for i in range(0, len(msg), 4096):
            sub_msg = msg[i:i + 4096]
            data = {
                "msgtype": "markdown",
                "markdown": {
                    "content": sub_msg
                }
            }
            url = WECHAT_MSG_URL
            resp = requests.post(url, json=data).text
            logger.debug(f"发送消息到企业微信：{resp} \n发送消息内容：\n{msg}")

    def patent_count_job(self):
        sql = f"""
        select '{datetime.datetime.now().date()}' as date, count(taxpayer_id) as count, '专利' as task_type
        from net_patent_task_log
        where update_time >= '{datetime.datetime.now().date()}'
        """
        datas = self.to_db.find(sql, to_json=True)
        self.wfq_source.add_batch_smart('spider_task_record', datas, update_columns=['count'])

    def black_count_job(self):
        sql = f"""
        select '{datetime.datetime.now().date()}' as date, count(taxpayer_id) as count, '黑名单' as task_type
        from net_black_task_log
        where update_time >= '{datetime.datetime.now().date()}'
        """
        datas = self.to_db.find(sql, to_json=True)
        self.wfq_source.add_batch_smart('spider_task_record', datas, update_columns=['count'])

    def statistics(self):
        """统计数据"""
        try:
            self.patent_count_job()
            self.black_count_job()
        except Exception as e:
            print(e)

    def send_statistics(self):
        self.statistics()
        sql = f"select task_type,date,count from spider_task_record where date = '{datetime.datetime.now().date()}'"
        datas = self.wfq_source.find(sql, to_json=True)
        self.wechat_send(self.create_table_with_pandas(datas, mappings={
            "task_type": "爬虫类型",
            "date": "日期",
            "count": "数量",
        }))


if __name__ == '__main__':
    scheduler = BlockingScheduler()
    test = SendWechatMsg()
    test.send_statistics()
    scheduler.add_job(test.send_statistics, 'cron', hour='9,15,18,23', minute=0)

    scheduler.start()
